IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_NewsFeed_Search')
	BEGIN
		DROP  Procedure  usp_NewsFeed_Search
	END

GO
/*---------------------------------------------------
Author   Date         Purpose
Shailen  20 Oct 2008  Searches for news feeds.
---------------------------------------------------*/
CREATE Procedure usp_NewsFeed_Search
	(
		@sectionId int = null,
		@sourceId int = null,
		@pageSize int = 10,
		@search varchar(300) = null,
		@currentPage int = 1,
		@startDate datetime = null,
		@endDate datetime = null,
		@sortBy int = null,
		@totalRecords int output
	)

AS

-- Turn off count
Set NoCount On

-- Declare variables.
Declare @FirstRec int
Declare @LastRec int

-- Initialize variables.
Set @FirstRec = (@CurrentPage - 1) * @PageSize
Set @LastRec = (@CurrentPage * @PageSize)

Create Table #PageTable
(
PagingId int IDENTITY PRIMARY KEY,
RowId int,
SourceId int null
--GroupSeqNo int null
)
DECLARE @SORT_BY_POPULARITY INT,
		@SORT_BY_FRESHNESS INT
		

SET @SORT_BY_POPULARITY = 0
SET @SORT_BY_FRESHNESS = 1

SET DATEFORMAT dmy

SELECT [NewsId] INTO #TEMP FROM [NewsFeedSection] 
WHERE	(@sectionId is null OR [SectionId] = @sectionId)

INSERT INTO #PageTable(RowId, SourceId)
SELECT	[Id], [Source]
FROM	[NewsFeed] (nolock)
WHERE	(@sourceId IS NULL OR [Source] = @sourceId)
		/* If the sectionId is not Breaking News or Popular stories, then it needs to be filtered from the NewsFeedSection table. */
AND		(@sectionId IS NULL OR [Id] IN (SELECT [NewsId] FROM #TEMP))
		/* If the sectionId is Breaking News, then restrict by dateUpdated. */
--AND		(@sectionId IS NULL OR @sectionId != @SECTION_BREAKING_NEWS OR DATEDIFF(hh, [DateUpdated], getdate()) < @BREAKING_NEWS_INTERVAL ) 
AND		/* If a search filter is specified, then apply filter */
		(@search IS NULL OR [Caption] LIKE @search OR [FullText] LIKE @search)
AND
		(@startDate IS NULL OR [DateUpdated] >= @startDate)
AND
		(@endDate IS NULL OR [DateUpdated] <= @endDate)
ORDER BY 
CASE
	/* Breaking News sort */
	WHEN (@sortBy = @SORT_BY_FRESHNESS)
	THEN (RANK() OVER (ORDER BY [DateUpdated] DESC, [Weighting] DESC, [HitCount] DESC, [DateCreated] DESC))
	/* Popular Stories sort */
	WHEN (@sortBy = @SORT_BY_POPULARITY) 
	THEN (RANK() OVER (ORDER BY [HitCount] DESC, [DateUpdated] DESC, [DateCreated] DESC, [Weighting] DESC))	
	/* Default sort when no source is specified */
	WHEN (@sourceId IS NULL)
	THEN (RANK() OVER (ORDER BY [DateUpdated] DESC, [Weighting] DESC, [HitCount] DESC, [DateCreated] DESC))	
	ELSE 
		(RANK() OVER (ORDER BY [DateUpdated] DESC, [Weighting] DESC, [HitCount] DESC, [DateCreated] DESC))
END

SELECT @totalRecords = count(1) 
FROM	#PageTable (nolock)

DELETE FROM #PageTable
WHERE PagingId < @FirstRec 
OR PagingId > @LastRec

SELECT	B.*
FROM
#PageTable (nolock) A
INNER JOIN 
[NewsFeed] (nolock) B
ON A.[RowId] = B.[Id]
ORDER BY A.[PagingId]

DROP TABLE #TEMP
DROP TABLE #PageTable

RETURN
GO


GRANT EXEC ON usp_NewsFeed_Search TO PUBLIC

GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_NewsFeed_HitCountUpdate')
	BEGIN
		DROP  Procedure  [dbo].[usp_NewsFeed_HitCountUpdate]
	END

GO

/****** Object:  StoredProcedure [dbo].[usp_NewsFeed_HitCountUpdate]    Script Date: 11/12/2008 17:56:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO+
CREATE PROCEDURE [dbo].[usp_NewsFeed_HitCountUpdate]
			@Id INT
AS

UPDATE [dbo].[NewsFeed]
SET [HitCount ] = [HitCount] + 1
WHERE [Id] = @Id
